Tuning Procedural Code

Procedural code, such as PL/SQL, is often a fallback position for running SQLs that are too complex to code in a single statement.

The typical model for such a module would be:

DECLARE
    CURSOR C1 IS ...
BEGIN
    FOR this_row IN C1 LOOP
        do_step1();
        do_step2();
        ...
		commit_every_1000_rows();
    END LOOP;
END;

Generally speaking, a single SQL can be made to run more efficiently than the equivalent PL/SQL routine that loops through a cursor. However this should not be seen as a challenge to write huge convoluted SQLs that are impossible to maintain.

Do you have a cursor within a cursor? Or a SELECT INTO within a cursor? The single greatest cause of inefficient procedural code of the type above is when SQL is performed inside the cursor loop. Prior to v9i and the introduction of bulk binding and the FORALL statement, it was necessary to perform INSERTs, UPDATEs, and DELETEs inside a cursor loop - this was a necessary evil. All cursors and SQL statements are equivalent to performing a Nested Loops join (except less efficient). For cursors looping through a large number of rows, this can have terrible consequences.

The first thing you should do is remove as many SQLs from inside the loop as possible by joining tables into the main cursor. For simple lookups with equals joins this should be easy. Use the other techniques in this guide to re-tune the main cursor if necessary. What remains should be SQL statements inside the loop that seem impossible to merge into the main cursor.

Below are a few tricks that help avoid SQL statements within a cursor loop:

• Analytic functions, the MERGE statement, and multi-table INSERT statements (INSERT ALL and INSERT FIRST) provide functionality not previously available in SQL. You may be able to re-write your PL/SQL as a single SQL if it is using a cursor to process rows in fixed sequence.
• Can't join two or more large tables because the join key is non-unique in both tables? ie. Table A has 4 rows per key, and Table B contains 7 rows per key - a join would return 28 rows. Try Concurrent Cursors instead of a cursor within a cursor.
• Tried running it as a single SQL but it was too big - you got the error:

  "Failed to allocate an extent of size n in tablespace TEMP"

  If the SQL is performing Hash joins, try Sort-Merge (use the USE_MERGE hint) joins instead. They can take less temp space. Note that if your tables are growing, then even if sort-merge works, it may also blow out one day. Also ask the DBA to consider increasing the temp space.
• Do you have an INSERT .. SELECT, a DELETE, or an UPDATE that was so big it exceeded your rollback segments, so you decided to write it in transactional PL/SQL. Take a look at the High Volume DML hints - you may yet be able to re-code it as a single SQL.
• Use FORALL to perform INSERT, UPDATE, and DELETE statements in bulk outside the cursor loop. The technique is detailed in the PL/SQL Users Guide and Reference; it involves looping through a cursor and saving results to a PL/SQL Nested Table, and then applying the changes to the database afterwards in a FORALL loop.

Eliminating SQL from inside cursor loops will provide the most significant time saving. For smaller but still significant improvements on high volume jobs, use BULK COLLECT to fetch cursors into Nested Tables thousands of rows at a time. This technique is detailed in the PL/SQL Users Guide and Reference. BULK COLLECT requires cursors to be explicitly declared, opened, fetched, and closed, so - for applications that use implicit cursor FOR loops - it requires a significant rewrite effort.